﻿using System.Collections.Generic;
using System.Data;
using Oracle.DataAccess.Client;
using Platform.Configuration;
using Platform.Data;
using System;
using System.Text;

namespace BLL
{
    public class SqlHelper
    {
        //添加用户自定义菜单
        public bool AddUserMenu(int userId, string menuId, string code)
        {
            var deleteSql = "DELETE FROM S_USERMENU WHERE USERID = :USERID AND CODE = :CODE";
            OracleParameter[] deleteParams = {new OracleParameter("USERID", userId),new OracleParameter("CODE",code) };
            OracleHelper.ExecuteNonQuery(ConfigHelper.PlatformConnString, CommandType.Text,
                deleteSql, deleteParams);
            if (menuId != "")
            {
                var insertSql = "INSERT INTO S_USERMENU (USERID,MENUID,CODE) VALUES (:USERID,:MENUID,:CODE)";
                OracleParameter[] insertParams =
                    {new OracleParameter("USERID", userId), new OracleParameter("MENUID", menuId),new OracleParameter("CODE",code)};
                var result = OracleHelper.ExecuteNonQuery(ConfigHelper.PlatformConnString, CommandType.Text,
                    insertSql, insertParams);
                return result == 1;
            }
            return true;
        }

        //获取用户自定义菜单
        public string GetUserMenu(int userId,string code)
        {
            var sql = "SELECT MENUID FROM S_USERMENU WHERE USERID = :USERID AND CODE = :CODE";
            OracleParameter[] selectParams = {new OracleParameter("USERID", userId),new OracleParameter("CODE",code) };
            var result = OracleHelper.ExecuteScalar(ConfigHelper.PlatformConnString, CommandType.Text,
                sql, selectParams);
            if (result != null)
                return result.ToString();
            return null;
        }

        //获取菜单的信息
        public DataSet GetMenus(string menuId)
        {
            var sql = "SELECT * FROM S_MENU WHERE MENUID IN (" + menuId + ")";
            var result = OracleHelper.ExecuteDataset(ConfigHelper.PlatformConnString,
                CommandType.Text,
                sql);
            return result;
        }

        //获取到账导出的信息
        public DataSet GetDaoZhang()
        {
            try
            {
                var sqlBak = @"insert into ys_sf_yongshuisf_out select a.*, sysdate from ys_sf_yongshuisf_tmp a";
                OracleHelper.ExecuteNonQuery(ConfigHelper.PlatformConnString, CommandType.Text, sqlBak);
            }
            catch (Exception) { }
            var sql = @"SELECT a.s_cbumen,a.s_cdianhua,a.s_ywy,a.s_cwaddr,a.s_cwpost,a.s_yonghum,a.s_kaihuhm,a.s_yonghuh,a.i_plan,a.i_sysl,a.i_jjsl,a.n_jjje,a.i_y,a.i_m,a.i_feiyongbh,a.i_dsbs,a.n_dsje 
                        FROM planwater_user.ys_sf_yongshuisf_tmp a ORDER BY a.s_cbumen, a.s_ywy, a.s_yonghuh";
            var result = OracleHelper.ExecuteDataset(ConfigHelper.PlatformConnString, CommandType.Text, sql);
            return result;
        }

        //执行销表操作
        public bool DeleteWaterMeter(string waterMeterNum, string userId)
        {
            var deleteSql =
                @"UPDATE ys_cb_yonghusbgx s SET s.i_jlzt=-1,s.i_shuibiaozt=5,s.s_xiugair=:UserId, s.d_caozuosj=sysdate,s.s_beiyong1='直接销表' WHERE s.i_jlzt=0 AND s.s_shuibiaobh = :WaterMeterNum";
            OracleParameter[] deleteParams =
                {new OracleParameter("UserId", userId), new OracleParameter("WaterMeterNum", waterMeterNum)};
            var result = OracleHelper.ExecuteNonQuery(ConfigHelper.PlatformConnString,
                CommandType.Text, deleteSql, deleteParams);
            if (result > 0)
            {
                deleteSql =
                    @"UPDATE YS_CB_SHUIBIAOXX S SET S.I_JLZT=-1,S.S_BEIZHU= '直接销表' WHERE S.I_JLZT=0 AND s.s_shuibiaobh = :WaterMeterNum";
                OracleParameter[] deleteParams2 = {new OracleParameter("WaterMeterNum", waterMeterNum)};
                result = OracleHelper.ExecuteNonQuery(ConfigHelper.PlatformConnString,
                    CommandType.Text, deleteSql, deleteParams2);
                return result > 0;
            }
            return false;
        }

        //执行销户操作
        public bool DeleteUser(string userNumber)
        {
            var deleteSql = @"UPDATE ys_yg_yonghuxx s SET s.i_yonghuzt=3,s.i_jlzt=-1 WHERE s_yonghuh= :USERNUMBER ";
            OracleParameter[] deleteParams = {new OracleParameter("USERNUMBER", userNumber)};
            var result = OracleHelper.ExecuteNonQuery(ConfigHelper.PlatformConnString,
                CommandType.Text, deleteSql, deleteParams);
            return result > 0;
        }

        public bool DeleteLaihan(string laihanbh)
        {
            var deleteSql = @"UPDATE ys_lh_laihanxx s SET s.i_jlzt=-1 WHERE i_shurur <> 99996 and s_laihanbh= :LAIHANBH ";
            OracleParameter[] deleteParams = { new OracleParameter("LAIHANBH", laihanbh) };
            var result = OracleHelper.ExecuteNonQuery(ConfigHelper.PlatformConnString,
                CommandType.Text, deleteSql, deleteParams);
            return result > 0;
        }

        //插入取水口历史信息
        public bool P_QS_INSERT_QUSHUIKOU_HIS(decimal? V_ID, string V_CAOZUOR, OracleTransaction trans)
        {
            OracleParameter[] storedParams;
            storedParams =
                OracleHelperParameterCache.GetSpParameterSet(ConfigHelper.PlatformConnString,
                    "P_QS_INSERT_QUSHUIKOU_HIS");
            storedParams[0].Value = V_ID;
            storedParams[1].Value = V_CAOZUOR;
            var result = 0;
            if (trans == null)
                result = OracleHelper.ExecuteNonQuery(ConfigHelper.PlatformConnString, CommandType.StoredProcedure,
                    "P_QS_INSERT_QUSHUIKOU_HIS", storedParams);
            else
                result = OracleHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "P_QS_INSERT_QUSHUIKOU_HIS",
                    storedParams);
            return result >= 1;
        }

        //插入深井历史信息
        public bool P_QS_INSERT_SHENJIN_HIS(decimal? V_ID, string V_CAOZUOR, OracleTransaction trans)
        {
            OracleParameter[] storedParams;
            storedParams =
                OracleHelperParameterCache.GetSpParameterSet(ConfigHelper.PlatformConnString,
                    "P_QS_INSERT_SHENJIN_HIS");
            storedParams[0].Value = V_ID;
            storedParams[1].Value = V_CAOZUOR;
            var result = 0;
            if (trans == null)
                result = OracleHelper.ExecuteNonQuery(ConfigHelper.PlatformConnString, CommandType.StoredProcedure,
                    "P_QS_INSERT_SHENJIN_HIS", storedParams);
            else
                result = OracleHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "P_QS_INSERT_SHENJIN_HIS",
                    storedParams);
            return result == 1;
        }

        //添加水表历史信息
        public bool P_QS_INSERT_SHUIBIAO_HIS(decimal? V_ID, string V_CAOZUOR, string V_TYPE,
            OracleTransaction trans)
        {
            OracleParameter[] storedParams;
            storedParams =
                OracleHelperParameterCache.GetSpParameterSet(ConfigHelper.PlatformConnString,
                    "P_QS_INSERT_SHUIBIAO_HIS");
            storedParams[0].Value = V_ID;
            storedParams[1].Value = V_CAOZUOR;
            storedParams[2].Value = V_TYPE;
            var result = 0;
            if (trans == null)
                result = OracleHelper.ExecuteNonQuery(ConfigHelper.PlatformConnString, CommandType.StoredProcedure,
                    "P_QS_INSERT_SHUIBIAO_HIS", storedParams);
            else
                result = OracleHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "P_QS_INSERT_SHUIBIAO_HIS",
                    storedParams);
            return result == 1;
        }

        //添加用户历史信息
        public bool P_QS_INSERT_YONGHU_HIS(decimal? V_ID, string V_CAOZUOR, string V_TYPE, OracleTransaction trans)
        {
            OracleParameter[] storedParams;
            storedParams =
                OracleHelperParameterCache.GetSpParameterSet(ConfigHelper.PlatformConnString, "P_QS_INSERT_YONGHU_HIS");
            storedParams[0].Value = V_ID;
            storedParams[1].Value = V_CAOZUOR;
            storedParams[2].Value = V_TYPE;
            var result = 0;
            if (trans == null)
                result = OracleHelper.ExecuteNonQuery(ConfigHelper.PlatformConnString, CommandType.StoredProcedure,
                    "P_QS_INSERT_YONGHU_HIS", storedParams);
            else
                result = OracleHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "P_QS_INSERT_YONGHU_HIS",
                    storedParams);
            return result >= 1;
        }

        //根据Code获取菜单信息
        public DataSet GetMenuByCode(string code)
        {
            var sql = "SELECT * FROM S_MENU WHERE CODE = :CODE";
            OracleParameter[] selectParams = {new OracleParameter("CODE", code)};
            var result = OracleHelper.ExecuteDataset(ConfigHelper.PlatformConnString, CommandType.Text,
                sql, selectParams);
            return result;
        }

        //获取用户权限
        public DataSet GetUserPermission(string type, int userId)
        {
            var sql = "SELECT * FROM S_USERPERMISSION WHERE TYPE=:TYPE AND USER_ID=:USERID";
            OracleParameter[] selectParams = {new OracleParameter("TYPE", type), new OracleParameter("USERID", userId)};
            var result = OracleHelper.ExecuteDataset(ConfigHelper.PlatformConnString, CommandType.Text,
                sql, selectParams);
            return result;
        }

        //删除用户权限
        public bool RemoveUserPermission(string type, int userId)
        {
            var sql = "DELETE FROM S_USERPERMISSION WHERE TYPE=:TYPE AND USER_ID=:USERID";
            OracleParameter[] selectParams = {new OracleParameter("TYPE", type), new OracleParameter("USERID", userId)};
            var result = OracleHelper.ExecuteNonQuery(ConfigHelper.PlatformConnString, CommandType.Text,
                sql, selectParams);
            return result > 0;
        }

        //添加用户权限
        public bool AddUserPermission(string type, int userId, int menuId)
        {
            var sql = "INSERT INTO S_USERPERMISSION (USER_ID,MENU_ID,TYPE) VALUES (:USERID,:MENUID,:TYPE)";
            OracleParameter[] selectParams =
            {
                new OracleParameter("USERID", userId), new OracleParameter("MENUID", menuId),
                new OracleParameter("TYPE", type)
            };
            var result = OracleHelper.ExecuteNonQuery(ConfigHelper.PlatformConnString, CommandType.Text,
                sql, selectParams);
            return result > 0;
        }

        //获取用户的角色权限
        public DataSet GetUserRolePermisson(int userId, string code)
        {
            var sql = @"SELECT * FROM s_menu WHERE permissionid IN (SELECT permissionid FROM
                as_permissionrolerelation WHERE roleid IN (SELECT roleid FROM as_userrolerelation a WHERE a.userid = :v_userid)) AND code = :v_code";
            OracleParameter[] selectParams =
                {new OracleParameter("v_userid", userId), new OracleParameter("v_code", code)};
            var result = OracleHelper.ExecuteDataset(ConfigHelper.PlatformConnString, CommandType.Text,
                sql, selectParams);
            return result;
        }

        //获取管理块信息
        public DataSet GetGuanLiKuai()
        {
            var sql = "SELECT * FROM YS_JX_GUANLIK WHERE I_JLZT = 0";
            var result = OracleHelper.ExecuteDataset(ConfigHelper.PlatformConnString, CommandType.Text,
                sql);
            return result;
        }

        //获取来函用户信息
        public DataSet GetLaiHanUser(string type, string startTime, string endTime)
        {
            var sql = @"SELECT
            a.s_laihanbh,b.s_yonghuh,b.s_yonghum,b.s_dizhi,b.s_youbian,b.s_lianxibm,b.s_lianxir,b.s_lianxidh FROM
            ys_lh_laihanxx a INNER JOIN ys_yg_yonghuxx b ON a.s_yonghubh = b.s_yonghuh WHERE a.i_jlzt = 0 AND b.i_leixing = 1";
            var selectParams = new List<OracleParameter>();
            if (startTime != "")
            {
                sql += " AND a.d_shenpirq > to_date(:startTime,'yy-mm-dd hh24:mi:ss')";
                selectParams.Add(new OracleParameter("startTime", startTime));
            }
            if (endTime != "")
            {
                sql += " AND a.d_shenpirq < to_date(:endTime,'yy-mm-dd hh24:mi:ss')";
                selectParams.Add(new OracleParameter("endTime", endTime));
            }
            if (type == "2,3,5")
            {
                sql += " AND a.i_laihanlx IN (" + type + ")";
            }
            else
            {
                sql += " AND a.i_laihanlx = :type";
                selectParams.Add(new OracleParameter("type", type));
            }
            sql += " ORDER BY b.s_yonghuh";
            var result = OracleHelper.ExecuteDataset(ConfigHelper.PlatformConnString, CommandType.Text,
                sql, selectParams.ToArray());
            return result;
        }

        public DataSet P_YS_GETUSERPLANE(string year, string guanlikuai, string skhm, string laihan,
            OracleTransaction trans)
        {
            var storedParams =
                OracleHelperParameterCache.GetSpParameterSet(ConfigHelper.PlatformConnString, "P_YS_EXPORTUSERPLAN");
            storedParams[0].Value = guanlikuai;
            storedParams[1].Value = year;
            storedParams[2].Value = skhm;
            storedParams[3].Value = laihan;
            DataSet result;
            if (trans == null)
                result = OracleHelper.ExecuteDataset(ConfigHelper.PlatformConnString, CommandType.StoredProcedure,
                    "P_YS_EXPORTUSERPLAN", storedParams);
            else
                result = OracleHelper.ExecuteDataset(trans, CommandType.StoredProcedure, "P_YS_EXPORTUSERPLAN",
                    storedParams);
            return result;
        }

        public DataSet GetJiHuaZXQK(string guanlikuai, string year, string month, string tuoshou, string tiaozheng)
        {
            var sql =
                @"SELECT a.s_yonghuh,a.I_SHIYONGSL,a.I_JIAJIAL,a.N_JIAJIAJE,b.s_yonghum FROM YS_JH_YONGHUYSSJ a INNER JOIN YS_YG_YONGHUXX b ON a.S_YONGHUH = b.S_YONGHUH WHERE a.I_CHAOJIHSL <>0 AND b.i_leixing = 0 AND ";
            var sqlWhere = @"a.i_y = :YEAR AND a.i_m = :MONTH";
            if (guanlikuai != "0")
                sqlWhere = sqlWhere + " AND a.S_YONGHUH LIKE '" + guanlikuai + "%'";
            if (tuoshou != "")
                sqlWhere = sqlWhere + " AND b.I_SHOUFEIFS = " + tuoshou;
            switch (tiaozheng)
            {
                case "0":
                    sqlWhere = sqlWhere + " AND a.I_CHUTIAOZ = 0";
                    break;
                case "1":
                    sqlWhere = sqlWhere + " AND a.I_CHUTIAOZ <> 0";
                    break;
            }
            sql = sql + sqlWhere + " ORDER BY a.S_YONGHUH";
            OracleParameter[] selectParams =
                {new OracleParameter("YEAR", year), new OracleParameter("MONTH", month)};
            var result = OracleHelper.ExecuteDataset(ConfigHelper.PlatformConnString, CommandType.Text,
                sql, selectParams);
            return result;
        }

        public DataSet GetYongHuZXQK(string yonghuh, string shuiliangym, OracleTransaction trans)
        {
            var storedParams =
                OracleHelperParameterCache.GetSpParameterSet(ConfigHelper.PlatformConnString, "P_JH_CHAOJIHUADY");
            var year = shuiliangym.Substring(0, 4);
            var month = shuiliangym.Substring(4, 2);
            storedParams[0].Value = yonghuh;
            storedParams[1].Value = year;
            storedParams[2].Value = month;
            DataSet result;
            if (trans == null)
                result = OracleHelper.ExecuteDataset(ConfigHelper.PlatformConnString, CommandType.StoredProcedure,
                    "P_JH_CHAOJIHUADY", storedParams);
            else
                result = OracleHelper.ExecuteDataset(trans, CommandType.StoredProcedure, "P_JH_CHAOJIHUADY",
                    storedParams);
            return result;
        }

        public void AddLaiHanSpx(string laihanbh, OracleTransaction trans)
        {
            OracleParameter[] storedParams;
            storedParams =
                OracleHelperParameterCache.GetSpParameterSet(ConfigHelper.PlatformConnString, "P_YS_LH_ADDLAIHANSPX");
            storedParams[0].Value = laihanbh;
            if (trans == null)
                OracleHelper.ExecuteNonQuery(ConfigHelper.PlatformConnString, CommandType.StoredProcedure,
                    "P_YS_LH_ADDLAIHANSPX", storedParams);
            else
                OracleHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "P_YS_LH_ADDLAIHANSPX",
                    storedParams);
        }

        public DataSet GetLaiHanLCJL(string laiHanBh)
        {
            var sql =
                "SELECT a.I_LIUCHENGBH,a.D_ENDDATE,a.S_CAOZUORQM FROM YS_LH_LAIHANLCJL a WHERE a.S_LAIHANBH = :LAIHANBH AND a.I_LIUCHENGBH>=3";
            OracleParameter[] selectParams =
            {
                new OracleParameter("LAIHANBH", laiHanBh)
            };
            var result = OracleHelper.ExecuteDataset(ConfigHelper.PlatformConnString, CommandType.Text,
                sql, selectParams);
            return result;
        }

        // 更新【到账导出】的导出状态
        public bool p_yssf_shuifeizd_update(int userid, out int o_status, out string o_msg,
            OracleTransaction trans = null)
        {
            OracleParameter[] storedParams = OracleHelperParameterCache.GetSpParameterSet(ConfigHelper.PlatformConnString, "p_yssf_shuifeizd_update");
            storedParams[0].Value = userid;
            var result = 0;
            if (trans == null)
                result = OracleHelper.ExecuteNonQuery(ConfigHelper.PlatformConnString, CommandType.StoredProcedure, "p_yssf_shuifeizd_update", storedParams);
            else
                result = OracleHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "p_yssf_shuifeizd_update", storedParams);
            o_status = Convert.ToInt32(storedParams[1].Value);
            o_msg = storedParams[2].Value.ToString();
            return result == 1;
        }


        //自来水数据转码（临时用）
        public void TranCodeZlsgssj()
        {
            Func<DataRow, string, bool> fTran = (DataRow dr, string sField) =>
            {
                try
                {
                    string sGBK = UnicodeToString(dr[sField].ToString());
                    string sGBK2 = gb2312_utf8(dr[sField].ToString());
                    string sqlup = "update YS_CB_ZILAISGSSJ a set " + sField + " = '" + sGBK + "' where S_SHUIBIAOBH = '"
                                 + dr["S_SHUIBIAOBH"] + "'";
                    OracleHelper.ExecuteDataset(ConfigHelper.PlatformConnString, CommandType.Text, sqlup);
                }
                catch (Exception ex)
                {
                }
                return true;
            };
            string sql = "SELECT * FROM YS_CB_ZILAISGSSJ where rownum < 10";
            DataSet ds = OracleHelper.ExecuteDataset(ConfigHelper.PlatformConnString, CommandType.Text, sql);
            if (null != ds && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    fTran(dr, "S_SHUIBIAODZ");
                    fTran(dr, "S_SHUIBIAOHM");
                    fTran(dr, "S_YUANCHAOBZT");
                }
        }

        private string GB2312ToUTF8(string str)
        {
            try
            {
                Encoding utf8 = Encoding.UTF8;
                Encoding gb2312 = Encoding.GetEncoding("GB2312");
                byte[] unicodeBytes = gb2312.GetBytes(str);
                byte[] asciiBytes = Encoding.Convert(gb2312, utf8, unicodeBytes);
                char[] asciiChars = new char[utf8.GetCharCount(asciiBytes, 0, asciiBytes.Length)];
                utf8.GetChars(asciiBytes, 0, asciiBytes.Length, asciiChars, 0);
                string result = new string(asciiChars);
                return result;
            }
            catch
            {
                return str;
            }
        }

        private string UTF8ToGB2312(string str)
        {
            try
            {
                Encoding utf8 = Encoding.GetEncoding("GB2312");
                Encoding gb2312 =  Encoding.UTF8;
                byte[] unicodeBytes = gb2312.GetBytes(str);
                byte[] asciiBytes = Encoding.Convert(gb2312, utf8, unicodeBytes);
                char[] asciiChars = new char[utf8.GetCharCount(asciiBytes, 0, asciiBytes.Length)];
                utf8.GetChars(asciiBytes, 0, asciiBytes.Length, asciiChars, 0);
                string result = new string(asciiChars);
                return result;
            }
            catch
            {
                return str;
            }
        }

        public static string gb2312_utf8(string text)
        {
            //声明字符集   
            System.Text.Encoding utf8, gb2312;
            //gb2312   
            gb2312 = System.Text.Encoding.GetEncoding("gb2312");
            //utf8   
            utf8 = System.Text.Encoding.Unicode;
            byte[] gb;
            gb = gb2312.GetBytes(text);
            gb = System.Text.Encoding.Convert(gb2312, utf8, gb);
            //返回转换后的字符   
            return utf8.GetString(gb);
        }

        public static string utf8_gb2312(string text)
        {
            //声明字符集   
            System.Text.Encoding utf8, gb2312;
            //utf8   
            utf8 = System.Text.Encoding.Unicode;
            //gb2312   
            gb2312 = System.Text.Encoding.GetEncoding("gb2312");
            byte[] utf;
            utf = utf8.GetBytes(text);
            utf = System.Text.Encoding.Convert(utf8, gb2312, utf);
            //返回转换后的字符   
            return gb2312.GetString(utf);
        }

        private string UnicodeToString(string unicode)
        {
            string resultStr = "";
            string[] strList = unicode.Split('u');
            for (int i = 1; i < strList.Length; i++)
            {
                resultStr += (char)int.Parse(strList[i], System.Globalization.NumberStyles.HexNumber);
            }
            return resultStr;
        }

        //删除表数据
        public bool TruncateTable(string sTableName)
        {
            var sql = "DELETE FROM " + sTableName;
            var result = OracleHelper.ExecuteNonQuery(ConfigHelper.PlatformConnString, CommandType.Text, sql);
            return result > 0;
        }
        public bool DeleteFromTable(string sTableName, string sWhere)
        {
            var sql = "DELETE FROM " + sTableName + " " + sWhere;
            var result = OracleHelper.ExecuteNonQuery(ConfigHelper.PlatformConnString, CommandType.Text, sql);
            return result > 0;
        }


        public DataSet P_YSCX_ZLSDYHQD(int ym, OracleTransaction trans = null)
        {
            var storedParams =
                OracleHelperParameterCache.GetSpParameterSet(ConfigHelper.PlatformConnString, "P_YSCX_ZLSDYHQD");
            storedParams[0].Value = ym;
            DataSet result;
            if (trans == null)
                result = OracleHelper.ExecuteDataset(ConfigHelper.PlatformConnString, CommandType.StoredProcedure,
                    "P_YSCX_ZLSDYHQD", storedParams);
            else
                result = OracleHelper.ExecuteDataset(trans, CommandType.StoredProcedure, "P_YSCX_ZLSDYHQD",
                    storedParams);
            return result;
        }

        public DataSet P_YS_LH_YUEDUTZJHHJCX(string V_ZHANDIAN, int? V_Y, int? V_M, int V_CAOZUOR)
        {
            using (OracleConnection connection = new OracleConnection(Platform.Configuration.ConfigHelper.PlatformConnString))
            {
                DataSet ds = null;
                OracleTransaction tran = null;
                try
                {
                    connection.Open();
                    tran = connection.BeginTransaction();
                    string sProcName = "P_YS_LH_YUEDUTZJHHJCX_ZZJ";
                    OracleParameter[] storedParams;
                    storedParams = OracleHelperParameterCache.GetSpParameterSet(Platform.Configuration.ConfigHelper.PlatformConnString, sProcName);
                    storedParams[0].Value = V_ZHANDIAN;
                    storedParams[1].Value = V_Y;
                    storedParams[2].Value = V_M;
                    storedParams[3].Value = V_CAOZUOR;
                    //DataSet ds = Maticsoft.DBUtility.OracleHelper.SP_QueryByTran(ConfigHelper.PlatformConnString, sProcName, storedParams);
                    ds = OracleHelper.ExecuteDataset(tran, CommandType.StoredProcedure, sProcName, storedParams);
                    tran.Commit();
                }
                catch (OracleException ex)
                {
                    tran.Rollback();
                    throw new Exception(ex.Message);
                }
                finally
                {
                    if (connection.State != ConnectionState.Closed) connection.Close();
                }
                return ds;
            }
        }

        public void P_YSZW_TUIKUANQR_AUTO(string V_LAIHANBH, OracleTransaction trans = null)
        {
            OracleParameter[] storedParams;
            storedParams = OracleHelperParameterCache.GetSpParameterSet(Platform.Configuration.ConfigHelper.PlatformConnString, "P_YSZW_TUIKUANQR_AUTO");
            storedParams[0].Value = V_LAIHANBH;
            if (trans == null)
                OracleHelper.ExecuteNonQuery(Platform.Configuration.ConfigHelper.PlatformConnString, CommandType.StoredProcedure, "P_YSZW_TUIKUANQR_AUTO", storedParams);
            else
                OracleHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "P_YSZW_TUIKUANQR_AUTO", storedParams);
        }

        public void P_YSJH_NIDINGJHCC_BEFORE(int V_Y, string V_YONGHUH, out int V_Err, out string V_Msg, OracleTransaction trans = null)
        {
            OracleParameter[] storedParams;
            storedParams = OracleHelperParameterCache.GetSpParameterSet(Platform.Configuration.ConfigHelper.PlatformConnString, "P_YSJH_NIDINGJHCC_BEFORE");
            storedParams[0].Value = V_Y;
            storedParams[1].Value = V_YONGHUH;
            if (trans == null)
                OracleHelper.ExecuteNonQuery(Platform.Configuration.ConfigHelper.PlatformConnString, CommandType.StoredProcedure, "P_YSJH_NIDINGJHCC_BEFORE", storedParams);
            else
                OracleHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, "P_YSJH_NIDINGJHCC_BEFORE", storedParams);
            if (storedParams[2].Value is System.DBNull)
            {
                V_Err = 0;
            }
            else
            {
                V_Err = Convert.ToInt32(storedParams[2].Value);
            }
            V_Msg = storedParams[3].Value is System.DBNull ? null : (string)storedParams[3].Value;
        }

        public DataSet P_YS_DINGEJIS(string V_YONGHUH, string V_SSDXDM, OracleTransaction trans = null)
        {
            string sProcName = "P_YS_DINGEJIS";
            OracleParameter[] storedParams;
            storedParams = OracleHelperParameterCache.GetSpParameterSet(Platform.Configuration.ConfigHelper.PlatformConnString, sProcName);
            storedParams[0].Value = V_YONGHUH;
            storedParams[1].Value = V_SSDXDM;
            if (trans == null)
                return OracleHelper.ExecuteDataset(Platform.Configuration.ConfigHelper.PlatformConnString, CommandType.StoredProcedure, sProcName, storedParams);
            else
                return OracleHelper.ExecuteDataset(trans, CommandType.StoredProcedure, sProcName, storedParams);
        }

        public DataSet P_YS_DINGEJIS_MX(string V_YONGHUH, int V_SORT, OracleTransaction trans = null)
        {
            string sProcName = "P_YS_DINGEJIS_MX";
            OracleParameter[] storedParams;
            storedParams = OracleHelperParameterCache.GetSpParameterSet(Platform.Configuration.ConfigHelper.PlatformConnString, sProcName);
            storedParams[0].Value = V_YONGHUH;
            storedParams[1].Value = V_SORT;
            if (trans == null)
                return OracleHelper.ExecuteDataset(Platform.Configuration.ConfigHelper.PlatformConnString, CommandType.StoredProcedure, sProcName, storedParams);
            else
                return OracleHelper.ExecuteDataset(trans, CommandType.StoredProcedure, sProcName, storedParams);
        }

        public DataSet P_YS_DINGEJIS_SYS(string V_YONGHUH, OracleTransaction trans = null)
        {
            string sProcName = "P_YS_DINGEJIS_SYS";
            OracleParameter[] storedParams;
            storedParams = OracleHelperParameterCache.GetSpParameterSet(Platform.Configuration.ConfigHelper.PlatformConnString, sProcName);
            storedParams[0].Value = V_YONGHUH;
            if (trans == null)
                return OracleHelper.ExecuteDataset(Platform.Configuration.ConfigHelper.PlatformConnString, CommandType.StoredProcedure, sProcName, storedParams);
            else
                return OracleHelper.ExecuteDataset(trans, CommandType.StoredProcedure, sProcName, storedParams);
        }

        public void P_YS_DINGEJIS_OP(string V_YONGHUH, OracleTransaction trans = null)
        {
            string sProcName = "P_YS_DINGEJIS_OP";
            OracleParameter[] storedParams;
            storedParams = OracleHelperParameterCache.GetSpParameterSet(Platform.Configuration.ConfigHelper.PlatformConnString, sProcName);
            storedParams[0].Value = V_YONGHUH;
            if (trans == null)
                OracleHelper.ExecuteNonQuery(Platform.Configuration.ConfigHelper.PlatformConnString, CommandType.StoredProcedure, sProcName, storedParams);
            else
                OracleHelper.ExecuteNonQuery(trans, CommandType.StoredProcedure, sProcName, storedParams);
        }

        public bool Update_YS_YG_DINGEJIS_SYS(string S_YONGHUH, int i_day,
             decimal n_k1, decimal n_k2, decimal n_k3, decimal n_k4, decimal n_k5, decimal n_k6,
             decimal n_kr1, decimal n_kr2, decimal n_kr3, decimal n_kr4, decimal n_kr5, decimal n_kr6)
        {
            var sql = @"update ys_yg_dingejis_sys a set i_day = :i_day,
                        n_k1 = :n_k1, n_k2 = :n_k2, n_k3 = :n_k3, n_k4 = :n_k4, n_k5 = :n_k5, n_k6 = :n_k6,
                        n_kr1 = :n_kr1, n_kr2 = :n_kr2, n_kr3 = :n_kr3, n_kr4 = :n_kr4, n_kr5 = :n_kr5, n_kr6 = :n_kr6
                        where S_YONGHUH = :S_YONGHUH
                        ";
            OracleParameter[] selectParams =
            {
                new OracleParameter("i_day", i_day),
                new OracleParameter("n_k1", n_k1),
                new OracleParameter("n_k2", n_k2),
                new OracleParameter("n_k3", n_k3),
                new OracleParameter("n_k4", n_k4),
                new OracleParameter("n_k5", n_k5),
                new OracleParameter("n_k6", n_k6),
                new OracleParameter("n_kr1", n_kr1),
                new OracleParameter("n_kr2", n_kr2),
                new OracleParameter("n_kr3", n_kr3),
                new OracleParameter("n_kr4", n_kr4),
                new OracleParameter("n_kr5", n_kr5),
                new OracleParameter("n_kr6", n_kr6),
                new OracleParameter("S_YONGHUH", S_YONGHUH),
            };
            var result = OracleHelper.ExecuteNonQuery(ConfigHelper.PlatformConnString, CommandType.Text,
                sql, selectParams);
            return result > 0;
        }

        public bool Update_YS_YG_DINGEJIS_BAS(string S_YONGHUH, string V_SSDXDM, int I_SORT, int I_SEL, decimal N_VAL1, decimal N_VAL2)
        {
            var sql = @"update YS_YG_DINGEJIS_BAS a set I_SEL = :I_SEL, N_VAL1 = :N_VAL1, N_VAL2 = :N_VAL2
                        where S_YONGHUH = :S_YONGHUH and S_SHESHUIDXDM = :S_SHESHUIDXDM and I_SORT = :I_SORT and i_jlzt = 0
                        ";
            OracleParameter[] selectParams =
            {
                new OracleParameter("I_SEL", I_SEL),
                new OracleParameter("N_VAL1", N_VAL1),
                new OracleParameter("N_VAL2", N_VAL2),
                new OracleParameter("S_YONGHUH", S_YONGHUH),
                new OracleParameter("S_SHESHUIDXDM", V_SSDXDM),
                new OracleParameter("I_SORT", I_SORT),
            };
            var result = OracleHelper.ExecuteNonQuery(ConfigHelper.PlatformConnString, CommandType.Text,
                sql, selectParams);
            return result > 0;
        }

        public void p_qsjh_jihuasr_dbs(string v_id, int v_y, int v_role,
            int v_sl1, int v_sl2, int v_sl3, int v_sl4, int v_sl5, int v_sl6,
            int v_sl7, int v_sl8, int v_sl9, int v_sl10, int v_sl11, int v_sl12,
            string v_ly, out int V_Err, out string V_Msg)
        {
            string sProcName = "p_qsjh_jihuasr_dbs";
            OracleParameter[] storedParams;
            storedParams = OracleHelperParameterCache.GetSpParameterSet(Platform.Configuration.ConfigHelper.PlatformConnString, sProcName);
            storedParams[0].Value = v_id;
            storedParams[1].Value = v_y;
            storedParams[2].Value = v_role;
            storedParams[3].Value = v_sl1;
            storedParams[4].Value = v_sl2;
            storedParams[5].Value = v_sl3;
            storedParams[6].Value = v_sl4;
            storedParams[7].Value = v_sl5;
            storedParams[8].Value = v_sl6;
            storedParams[9].Value = v_sl7;
            storedParams[10].Value = v_sl8;
            storedParams[11].Value = v_sl9;
            storedParams[12].Value = v_sl10;
            storedParams[13].Value = v_sl11;
            storedParams[14].Value = v_sl12;
            storedParams[15].Value = v_ly;
            OracleHelper.ExecuteNonQuery(Platform.Configuration.ConfigHelper.PlatformConnString, CommandType.StoredProcedure, sProcName, storedParams);
            V_Err = storedParams[16].Value is System.DBNull ? 0 : Convert.ToInt32(storedParams[16].Value);
            V_Msg = storedParams[17].Value is System.DBNull ? null : (string)storedParams[17].Value;
        }

        public DataSet p_qsjh_jihua_DBS(int V_BIAOSHI, int v_work_type, int v_role, int V_ID, int V_Y)
        {
            string sProcName = "p_qsjh_jihua_DBS";
            OracleParameter[] storedParams;
            storedParams = OracleHelperParameterCache.GetSpParameterSet(Platform.Configuration.ConfigHelper.PlatformConnString, sProcName);
            storedParams[0].Value = V_BIAOSHI;
            storedParams[1].Value = v_work_type;
            storedParams[2].Value = v_role;
            storedParams[3].Value = V_ID;
            storedParams[4].Value = V_Y;
            return OracleHelper.ExecuteDataset(Platform.Configuration.ConfigHelper.PlatformConnString, CommandType.StoredProcedure, sProcName, storedParams);
        }

        public void P_YS_ZW_DUOSTOGWZ(string V_YONGHUH, int V_YM, int V_ZWYM, out int V_Err, out string V_Msg)
        {
            string sProcName = "P_YS_ZW_DUOSTOGWZ";
            OracleParameter[] storedParams;
            storedParams = OracleHelperParameterCache.GetSpParameterSet(Platform.Configuration.ConfigHelper.PlatformConnString, sProcName);
            storedParams[0].Value = V_YONGHUH;
            storedParams[1].Value = V_YM;
            storedParams[2].Value = V_ZWYM;
            OracleHelper.ExecuteNonQuery(Platform.Configuration.ConfigHelper.PlatformConnString, CommandType.StoredProcedure, sProcName, storedParams);
            V_Err = storedParams[3].Value is System.DBNull ? 0 : Convert.ToInt32(storedParams[3].Value);
            V_Msg = storedParams[4].Value is System.DBNull ? null : (string)storedParams[4].Value;
        }

        public void P_YSSF_CHECKJQTUOSHOUJZD(string V_YONGHUH, int V_YM, int V_JZD, out int V_Err, out string V_Msg)
        {
            string sProcName = "P_YSSF_CHECKJQTUOSHOUJZD";
            OracleParameter[] storedParams;
            storedParams = OracleHelperParameterCache.GetSpParameterSet(Platform.Configuration.ConfigHelper.PlatformConnString, sProcName);
            storedParams[0].Value = V_YONGHUH;
            storedParams[1].Value = V_YM;
            storedParams[2].Value = V_JZD;
            OracleHelper.ExecuteNonQuery(Platform.Configuration.ConfigHelper.PlatformConnString, CommandType.StoredProcedure, sProcName, storedParams);
            V_Err = storedParams[3].Value is System.DBNull ? 0 : Convert.ToInt32(storedParams[3].Value);
            V_Msg = storedParams[4].Value is System.DBNull ? null : (string)storedParams[4].Value;
        }

        public void qs_sf_daozhang(DateTime D_DAOZHANGRQ,int S_RIQI,DateTime S_HEXIAORI, int S_FAPIAOHMs)
        {
            var sql = @"update qs_sf_daozhang a
                                set  D_DAOZHANGRQ = :D_DAOZHANGRQ
                            where S_FAPIAOHM = :S_FAPIAOHMs
                        ";
            OracleParameter[] selectParams =
            {
                new OracleParameter("D_DAOZHANGRQ", D_DAOZHANGRQ),
                  new OracleParameter("S_FAPIAOHMs", S_FAPIAOHMs),
            };
            var result = OracleHelper.ExecuteNonQuery(ConfigHelper.PlatformConnString, CommandType.Text,
                sql, selectParams);
            //{"ORA-00904: \"S_FAPIAOHMS\": 标识符无效"}
            //ORA-01747: user.table.column, table.column 或列说明无效；
            //OracleParameter 对象已经包含在集合中

            var sql1 = @"update qs_sf_daozhang a
                                        set        S_RIQI = :S_RIQI   
                                  where S_RIQI is null and S_FAPIAOHM = :S_FAPIAOHMs
                        ";
            OracleParameter[] selectParams1 =
            {
                new OracleParameter("S_RIQI", S_RIQI),
                   new OracleParameter("S_FAPIAOHMs", S_FAPIAOHMs),
            };
            var result1 = OracleHelper.ExecuteNonQuery(ConfigHelper.PlatformConnString, CommandType.Text,
                sql1, selectParams1);
            
            //return result > 0;
        }


        public DataSet P_DXSSF_HGTF_SP_LIST(int V_PAGE, int V_Y, int V_M, string V_ID, int V_Type)
        {
            string sProcName = "P_DXSSF_HGTF_SP_LIST";
            OracleParameter[] storedParams;
            storedParams = OracleHelperParameterCache.GetSpParameterSet(Platform.Configuration.ConfigHelper.PlatformConnString, sProcName);
            storedParams[0].Value = V_PAGE;
            storedParams[1].Value = V_Y;
            storedParams[2].Value = V_M;
            storedParams[3].Value = V_ID;
            storedParams[4].Value = V_Type;
            return OracleHelper.ExecuteDataset(Platform.Configuration.ConfigHelper.PlatformConnString, CommandType.StoredProcedure, sProcName, storedParams);
        }

        public int P_DXSSF_HGTF_SP_ADD(int V_OP, string V_ID, int V_SQL)
        {
            string sProcName = "P_DXSSF_HGTF_SP_ADD";
            OracleParameter[] storedParams;
            storedParams = OracleHelperParameterCache.GetSpParameterSet(Platform.Configuration.ConfigHelper.PlatformConnString, sProcName);
            storedParams[0].Value = V_OP;
            storedParams[1].Value = V_ID;
            storedParams[2].Value = V_SQL;
            return OracleHelper.ExecuteNonQuery(Platform.Configuration.ConfigHelper.PlatformConnString, CommandType.StoredProcedure, sProcName, storedParams);
        }

    }
}